import json
import sqlite3
from datetime import  datetime
json_data_path = "./data.json"
data_db = "./data.db"
# 建立数据库连接
connection = sqlite3.connect("library_system.db")
cursor = connection.cursor()

try:
    with open(data_db, "r", encoding="utf-8") as f:
        sql_commands = f.read()
        # cursor.executescript(sql_commands)
        connection.execute("PRAGMA encoding = 'UTF-8'")
        connection.commit()
        print("SQL commands executed successfully.")

    # 打开并读取JSON数据
    with open(json_data_path, "r", encoding="utf-8") as f:
        data = json.load(f)
        users = data["users"]
        books = data["books"]
except Exception as e:
    print(f"错误{e}")

# 插入图书数据
for book in books:
    try:
        # 使用默认值替换NaN值
        book_id = book.get("book_id", f"{datetime.now().timestamp()}")
        book_name = book.get("book_name", "Unknown Title")
        ISBN = book.get("ISBN", "0000000000")
        record_time = book.get("record_time", "2024.01.01 00:00:00")
        category = book.get("category", "General")
        author = book.get("author", "Unknown Author")
        publisher = book.get("publisher", "Unknown Publisher")
        publication_date = book.get("publication_date", "2024.01")
        borrowed_by = book.get("borrowed_by", " ")
        borrow_time = book.get("borrow_time", " ")
        loan_period = book.get("loan_period", "30")
        due_time = book.get("due_time", " ")
        img_url = book.get("img_url", " ")

        # 构建插入图书的SQL语句
        sql = "INSERT INTO books (book_id, book_name, ISBN, record_time, category, author, publisher, " \
              "publication_date, borrowed_by, borrow_time, loan_period, due_time, img_url) " \
              "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        cursor.execute(sql, (book_id, book_name, ISBN, record_time, category, author, publisher,
                             publication_date, borrowed_by, borrow_time, loan_period, due_time, img_url))

        # 提交事务
        connection.commit()

    except Exception as e:
        print(f"Error inserting book: {e}")
        connection.rollback()  # 发生异常时回滚事务
        continue  # 继续处理下一本书籍


